﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Data;
using System.Data.SqlClient;


namespace ConvertTools
{
    public class ProcessDataBll
    {
        public delegate void ProcessHandler(string Message);
        public event ProcessHandler ProcessEvent;

        public void RaiseProcessEvent(string Message)
        {
            if (ProcessEvent != null) ProcessEvent(Message);
        }



        public ExcelSheet GetExcelSheets(string Id, int StartLine, int EndLine)
        {
            if (Id.Equals("SO_DANG_KY_BANG_HINH_BANG_TU"))
            {
                return
                       new ExcelSheet("SO_DANG_KY_BANG_HINH_BANG_TU", StartLine, EndLine,
                                new MapField[]
                                {
                                    //new MapField("A","InventoryCode",System.Data.SqlDbType.NVarChar,null),
                                    new MapField("A","InventoryNumber",System.Data.SqlDbType.NVarChar,null),
                                    new MapField("B","RegDate",System.Data.SqlDbType.DateTime,null),
                                    new MapField("C","Subject",System.Data.SqlDbType.NVarChar,null),
                                    new MapField("D","Quantity",System.Data.SqlDbType.Int,null),
                                    new MapField("F","Size",System.Data.SqlDbType.NVarChar,null),
                                    new MapField("G","AssetType",System.Data.SqlDbType.NVarChar,null),
                                    new MapField("H","frkAssetStatusId",System.Data.SqlDbType.VarChar,new LookUpObject("TableAssetStatus","AssetStatusId","AssetStatusName","frkAssetStatusId")),
                                    new MapField("I","MadeBy",System.Data.SqlDbType.NText,new LookUpObject("TableAssetMadeBy","AssetMadeById","AssetMadeByName","TableAssetListMadeBy","AssetListMadeById","frkAssetMadeById","frkAssetListId")),
                                    new MapField("J","MadePlace",System.Data.SqlDbType.NVarChar,null),
                                    new MapField("K","StoragePlace",System.Data.SqlDbType.NVarChar,null),
                                    new MapField("L","Note",System.Data.SqlDbType.NVarChar,null),
                                    new MapField("frkAssetTypeId","banghinh"),
                                    new MapField("ClassCode","banghinh"),
                                }, "A", "banghinh"
                            );
            }

            if (Id.Equals("SO_DANG_KY_BANG_HINH_BANG_TU_TRUYEN_THONG"))
            {
                return
                       new ExcelSheet("SO_DANG_KY_BANG_HINH_BANG_TU_TRUYEN_THONG", StartLine, EndLine,
                                new MapField[]
                                {
                                    new MapField("A","InventoryNumber",System.Data.SqlDbType.NVarChar,null),
                                    //new MapField("A","InventoryNumber",System.Data.SqlDbType.NVarChar,null),
                                    new MapField("B","RegDate",System.Data.SqlDbType.DateTime,null),
                                    new MapField("C","Subject",System.Data.SqlDbType.NVarChar,null),
                                    new MapField("D","Quantity",System.Data.SqlDbType.Int,null),
                                    new MapField("F","Size",System.Data.SqlDbType.NVarChar,null),
                                    new MapField("G","AssetType",System.Data.SqlDbType.NVarChar,null),
                                    new MapField("H","frkAssetStatusId",System.Data.SqlDbType.VarChar,new LookUpObject("TableAssetStatus","AssetStatusId","AssetStatusName","frkAssetStatusId")),
                                    new MapField("I","MadeBy",System.Data.SqlDbType.NText,new LookUpObject("TableAssetMadeBy","AssetMadeById","AssetMadeByName","TableAssetListMadeBy","AssetListMadeById","frkAssetMadeById","frkAssetListId")),
                                    new MapField("J","MadePlace",System.Data.SqlDbType.NVarChar,null),
                                    new MapField("K","StoragePlace",System.Data.SqlDbType.NVarChar,null),
                                    new MapField("L","Note",System.Data.SqlDbType.NVarChar,null),
                                    new MapField("frkAssetTypeId","banghinhtruyenthong"),
                                    new MapField("ClassCode","banghinhtruyenthong"),
                                }, "A", "banghinhtruyenthong"
                            );
            }

            if (Id.Equals("SO_DANG_KY_BANG_GHI_AM"))
            {
                return
                       new ExcelSheet("SO_DANG_KY_BANG_GHI_AM", StartLine, EndLine,
                                new MapField[]
                                {
                                    //new MapField("A","InventoryCode",System.Data.SqlDbType.NVarChar,null),
                                    new MapField("A","InventoryNumber",System.Data.SqlDbType.NVarChar,null),
                                    new MapField("B","RegDate",System.Data.SqlDbType.DateTime,null),
                                    new MapField("C","Subject",System.Data.SqlDbType.NVarChar,null),
                                    new MapField("D","Quantity",System.Data.SqlDbType.Int,null),
                                    new MapField("F","Size",System.Data.SqlDbType.NVarChar,null),
                                    new MapField("G","AssetType",System.Data.SqlDbType.NVarChar,null),
                                    new MapField("H","frkAssetStatusId",System.Data.SqlDbType.VarChar,new LookUpObject("TableAssetStatus","AssetStatusId","AssetStatusName","frkAssetStatusId")),
                                    new MapField("I","MadeBy",System.Data.SqlDbType.NText,new LookUpObject("TableAssetMadeBy","AssetMadeById","AssetMadeByName","TableAssetListMadeBy","AssetListMadeById","frkAssetMadeById","frkAssetListId")),
                                    new MapField("J","MadePlace",System.Data.SqlDbType.NVarChar,null),
                                    new MapField("K","StoragePlace",System.Data.SqlDbType.NVarChar,null),
                                    new MapField("L","Note",System.Data.SqlDbType.NVarChar,null),
                                    new MapField("frkAssetTypeId","bangghiam"),
                                    new MapField("ClassCode","bangghiam"),
                                }, "A", "bangghiam"
                            );
            }

            if (Id.Equals("SO_DANG_KY_BANG_GHI_AM_TRUYEN_THONG"))
            {
                return
                       new ExcelSheet("SO_DANG_KY_BANG_GHI_AM_TRUYEN_THONG", StartLine, EndLine,
                                new MapField[]
                                {
                                    new MapField("A","InventoryNumber",System.Data.SqlDbType.NVarChar,null),
                                    //new MapField("A","InventoryNumber",System.Data.SqlDbType.NVarChar,null),
                                    new MapField("B","RegDate",System.Data.SqlDbType.DateTime,null),
                                    new MapField("C","Subject",System.Data.SqlDbType.NVarChar,null),
                                    new MapField("D","Quantity",System.Data.SqlDbType.Int,null),
                                    new MapField("F","Size",System.Data.SqlDbType.NVarChar,null),
                                    new MapField("G","AssetType",System.Data.SqlDbType.NVarChar,null),
                                    new MapField("H","frkAssetStatusId",System.Data.SqlDbType.VarChar,new LookUpObject("TableAssetStatus","AssetStatusId","AssetStatusName","frkAssetStatusId")),
                                    new MapField("I","MadeBy",System.Data.SqlDbType.NText,new LookUpObject("TableAssetMadeBy","AssetMadeById","AssetMadeByName","TableAssetListMadeBy","AssetListMadeById","frkAssetMadeById","frkAssetListId")),
                                    new MapField("J","MadePlace",System.Data.SqlDbType.NVarChar,null),
                                    new MapField("K","StoragePlace",System.Data.SqlDbType.NVarChar,null),
                                    new MapField("L","Note",System.Data.SqlDbType.NVarChar,null),
                                    new MapField("frkAssetTypeId","bangghiamtruyenthong"),
                                    new MapField("ClassCode","bangghiamtruyenthong"),
                                }, "A", "bangghiamtruyenthong"
                            );
            }


            if (Id.Equals("SO_DANG_KY_PHIM"))
            {
                return
                    new ExcelSheet("SO_DANG_KY_PHIM", StartLine, EndLine,
                            new MapField[]
                            {
                                //new MapField("A","InventoryCode",System.Data.SqlDbType.NVarChar,null),
                                new MapField("A","InventoryNumber",System.Data.SqlDbType.NVarChar,null),
                                new MapField("B","RegDate",System.Data.SqlDbType.DateTime,null),
                                new MapField("C","Subject",System.Data.SqlDbType.NVarChar,null),
                                new MapField("D","Quantity",System.Data.SqlDbType.Int,null),
                                new MapField("E","Size",System.Data.SqlDbType.NVarChar,null),
                                new MapField("F","AssetType",System.Data.SqlDbType.NVarChar,null),
                                new MapField("G","frkAssetStatusId",System.Data.SqlDbType.VarChar,new LookUpObject("TableAssetStatus","AssetStatusId","AssetStatusName","frkAssetStatusId")),
                                new MapField("G","AssetStatus",System.Data.SqlDbType.NVarChar,null),
                                new MapField("H","MadeBy",System.Data.SqlDbType.NText,new LookUpObject("TableAssetMadeBy","AssetMadeById","AssetMadeByName","TableAssetListMadeBy","AssetListMadeById","frkAssetMadeById","frkAssetListId")),
                                new MapField("I","MadePlace",System.Data.SqlDbType.NVarChar,null),
                                new MapField("J","StoragePlace",System.Data.SqlDbType.NVarChar,null),
                                new MapField("K","Note",System.Data.SqlDbType.NVarChar,null),
                                new MapField("frkAssetTypeId","phim"),
                                new MapField("ClassCode","phim")
                            }, "A", "phim"
                        );
            }

            if (Id.Equals("SO_DANG_KY_PHIM_TRUYEN_THONG"))
            {
                return
                    new ExcelSheet("SO_DANG_KY_PHIM_TRUYEN_THONG", StartLine, EndLine,
                            new MapField[]
                            {
                                //new MapField("A","InventoryCode",System.Data.SqlDbType.NVarChar,null),
                                new MapField("A","InventoryNumber",System.Data.SqlDbType.NVarChar,null),
                                new MapField("B","RegDate",System.Data.SqlDbType.DateTime,null),
                                new MapField("C","Subject",System.Data.SqlDbType.NVarChar,null),
                                new MapField("D","Quantity",System.Data.SqlDbType.Int,null),
                                new MapField("E","Size",System.Data.SqlDbType.NVarChar,null),
                                new MapField("F","AssetType",System.Data.SqlDbType.NVarChar,null),
                                new MapField("G","frkAssetStatusId",System.Data.SqlDbType.VarChar,new LookUpObject("TableAssetStatus","AssetStatusId","AssetStatusName","frkAssetStatusId")),
                                new MapField("G","AssetStatus",System.Data.SqlDbType.NVarChar,null),
                                new MapField("H","MadeBy",System.Data.SqlDbType.NText,new LookUpObject("TableAssetMadeBy","AssetMadeById","AssetMadeByName","TableAssetListMadeBy","AssetListMadeById","frkAssetMadeById","frkAssetListId")),
                                new MapField("I","MadePlace",System.Data.SqlDbType.NVarChar,null),
                                new MapField("J","StoragePlace",System.Data.SqlDbType.NVarChar,null),
                                new MapField("K","Note",System.Data.SqlDbType.NVarChar,null),
                                new MapField("frkAssetTypeId","phimtruyenthong"),
                                new MapField("ClassCode","phimtruyenthong")
                            }, "A", "phimtruyenthong"
                        );
            }


            if (Id.Equals("SO_KIEM_KE_BAN_DAU"))
            {
                return
                        new ExcelSheet("SO_KIEM_KE_BAN_DAU", StartLine, EndLine,
                            new MapField[]
                            {
                                new MapField("A","InventoryCode",System.Data.SqlDbType.NVarChar,null),
                                new MapField("B","InventoryNumber",System.Data.SqlDbType.NVarChar,null),                                
                                new MapField("C","ClassCode",System.Data.SqlDbType.NVarChar,null),
                                new MapField("D","Characteristic",System.Data.SqlDbType.NVarChar,null),
                                new MapField("E","AssetStatus",System.Data.SqlDbType.NVarChar,null),
                                new MapField("F","TechnicalMade",System.Data.SqlDbType.NVarChar,null),
                                new MapField("G","RegDate",System.Data.SqlDbType.DateTime,null),
                                new MapField("H","EntryDate ",System.Data.SqlDbType.DateTime,null),
                                new MapField("I","Report",System.Data.SqlDbType.NVarChar,null),
                                new MapField("L","Subject",System.Data.SqlDbType.NVarChar,null),
                                new MapField("M","OtherSubject",System.Data.SqlDbType.NVarChar,null),
                                new MapField("N","Quantity",System.Data.SqlDbType.Int,null),
                                new MapField("O","frkAssetNationalId",System.Data.SqlDbType.VarChar,new LookUpObject("TableAssetNational","AssetNationalId","AssetNationalName","frkAssetNationalId")),
                                new MapField("O","NationalName",System.Data.SqlDbType.NVarChar,null),
                                new MapField("P","frkAssetMaterialId",System.Data.SqlDbType.VarChar,new LookUpObject("TableAssetMaterial","AssetMaterialId","AssetMaterialName","frkAssetMaterialId")),
                                new MapField("P","Material",System.Data.SqlDbType.NVarChar,null),
                                //new MapField("N","AssetType",SqlDbType.NVarChar,null),
                                new MapField("R","Size",System.Data.SqlDbType.NVarChar,null),
                                new MapField("S","ContentHistory",System.Data.SqlDbType.NVarChar,null),
                                new MapField("T","FicDescription",System.Data.SqlDbType.NVarChar,null),
                                new MapField("U","Description",System.Data.SqlDbType.NVarChar,null),
                                new MapField("K","ProfileCode",System.Data.SqlDbType.VarChar,null),
                                new MapField("J","CollectDate",System.Data.SqlDbType.DateTime,null),
                                new MapField("H","InputDate",System.Data.SqlDbType.DateTime,null),                                
                                new MapField("Q","StoragePlace",System.Data.SqlDbType.NVarChar,null),
                                new MapField("V","Note",System.Data.SqlDbType.NVarChar,null),
                                //new MapField("W","ProfileCode",System.Data.SqlDbType.NVarChar,null),
                                new MapField("frkAssetTypeId","other")                                
                            }, "B", "other"
                        );
            }

            if (Id.Equals("SO_BAO_QUAN_TAM_THOI"))
            {
                return
                        new ExcelSheet("SO_BAO_QUAN_TAM_THOI", StartLine, EndLine,
                            new MapField[]
                            {
                                new MapField("A","InventoryNumber",System.Data.SqlDbType.NVarChar,null),
                                new MapField("B","EntryDate ",System.Data.SqlDbType.DateTime,null),
                                new MapField("C","Subject",System.Data.SqlDbType.NVarChar,null),
                                new MapField("D","Quantity",System.Data.SqlDbType.Int,null),
                                new MapField("E","Size",System.Data.SqlDbType.NVarChar,null),
                                new MapField("F","frkAssetMaterialId",System.Data.SqlDbType.VarChar,new LookUpObject("TableAssetMaterial","AssetMaterialId","AssetMaterialName","frkAssetMaterialId")),
                                new MapField("F","Material",System.Data.SqlDbType.NVarChar,null),
                                new MapField("G","AssetStatus",System.Data.SqlDbType.NVarChar,null),
                                new MapField("H","DeliverBy",System.Data.SqlDbType.NVarChar,null),
                                new MapField("I","ReceiveBy",System.Data.SqlDbType.NVarChar,null),
                                new MapField("J","UseFor",System.Data.SqlDbType.NVarChar,null),
                                new MapField("K","Note",System.Data.SqlDbType.NVarChar,null),
                                new MapField("frkAssetTypeId","sobaoquantamthoi"),                                
                            }, "A", "sobaoquantamthoi"
                        );
            }

            if (Id.Equals("SO_NHAP_TAI_LIEU_KHOA_HOC_PHU"))
            {
                return
                        new ExcelSheet("SO_NHAP_TAI_LIEU_KHOA_HOC_PHU", StartLine, EndLine,
                            new MapField[]
                            {
                                new MapField("A","InventoryNumber ",System.Data.SqlDbType.VarChar,null),
                                new MapField("A","InventoryCode ",System.Data.SqlDbType.VarChar,null),                                
                                new MapField("B","RegDate",System.Data.SqlDbType.DateTime,null),
                                new MapField("C","Subject",System.Data.SqlDbType.NVarChar,null),
                                new MapField("D","Quantity",System.Data.SqlDbType.Int,null),
                                new MapField("E","Characteristic",System.Data.SqlDbType.NVarChar,null),                                
                                new MapField("F","frkAssetMaterialId",System.Data.SqlDbType.VarChar,new LookUpObject("TableAssetMaterial","AssetMaterialId","AssetMaterialName","frkAssetMaterialId"),"Material"),                                
                                new MapField("F","AssetStatus",System.Data.SqlDbType.NVarChar,null),                                
                                new MapField("H","UseFor",System.Data.SqlDbType.NVarChar,null),
                                new MapField("I","Cost",System.Data.SqlDbType.Decimal,null),
                                new MapField("J","Note",System.Data.SqlDbType.NVarChar,null),
                                new MapField("G","DeliverBy",System.Data.SqlDbType.NVarChar,null),                                
                                new MapField("frkAssetTypeId","tailieukhoahocphu"),                                
                            }, "A", "tailieukhoahocphu"
                        );
            }
            return null;
        }


        public string GetAssTypeId(string Id)
        {
            if (Id.Equals("SO_DANG_KY_BANG_HINH_BANG_TU"))
            {
                return "banghinh";
            }

            if (Id.Equals("SO_DANG_KY_BANG_GHI_AM"))
            {
                return "bangghiam";
            }

            if (Id.Equals("SO_DANG_KY_BANG_GHI_AM_TRUYEN_THONG"))
            {
                return "bangghiamtruyenthong";
            }

            if (Id.Equals("SO_DANG_KY_BANG_HINH_BANG_TU_TRUYEN_THONG"))
            {
                return
                       "banghinhtruyenthong";
            }

            if (Id.Equals("SO_DANG_KY_PHIM"))
            {
                return
                    "phim";
            }

            if (Id.Equals("SO_DANG_KY_PHIM_TRUYEN_THONG"))
            {
                return
                    "phimtruyenthong";
            }

            if (Id.Equals("SO_KIEM_KE_BAN_DAU"))
            {
                return "other";
            }

            if (Id.Equals("SO_BAO_QUAN_TAM_THOI"))
            {
                return
                        "sobaoquantamthoi";

            }

            if (Id.Equals("SO_NHAP_TAI_LIEU_KHOA_HOC_PHU"))
            {
                return "tailieukhoahocphu";
            }
            return null;
        }


        string ConnectionString
        {
            get
            {
                string XmlFile = System.Windows.Forms.Application.StartupPath + "\\Config.xml";
                DataSet ds = new DataSet();
                string ItemConnectionString = "Data Source=.;Initial Catalog=TN.AssetMan;Integrated Security=false;User Id=sa; password=xyz1234; Pooling=true;Max Pool Size=10000;";
                if (System.IO.File.Exists(XmlFile) == false)
                {
                    ds.Tables.Add("info");
                    ds.Tables["Info"].Columns.Add("ConnectionString");
                    ds.Tables["Info"].Rows.Add(new object[]{
                        ItemConnectionString
                    });
                    ds.WriteXml(XmlFile);
                }
                else
                {
                    ds.ReadXml(XmlFile);
                    ItemConnectionString = (string)ds.Tables[0].Rows[0]["ConnectionString"];
                }
                return ItemConnectionString;
            }
        }

        string ConvertSortField(string field)
        {
            string[] Items = field.Split(new char[] { '.' }, StringSplitOptions.RemoveEmptyEntries);
            if (Items.Length == 1)
            {
                field = field + ".0";
                Items = field.Split(new char[] { '.' }, StringSplitOptions.RemoveEmptyEntries);
            }
            string Data = "";
            for (int iIndex = 0; iIndex < Items.Length; iIndex++)
            {
                if (iIndex > 0)
                {
                    Data = Data + ".";
                }
                Data = Data + Items[iIndex].PadLeft(5, '0');
            }
            for (int iIndex = Items.Length; iIndex <= 5; iIndex++)
            {
                Data = "".PadLeft(5, '0') + "." + Data;
            }
            return Data;
        }

        public void Execute(ExcelSheet OExcelSheet, string ExcelFile)
        {
            Application _excelApp = new Application();
            Workbook workBook = _excelApp.Workbooks.Open(ExcelFile);
            _excelApp.Visible = true;
            string SessionId = System.DateTime.Now.ToString("dd-MM-yyyy-HH:mm:ss");
            SqlTransaction oSqlTransaction = null;
            string DayMonthYear = System.DateTime.Now.ToString("dd/MM/yyyy");
            int StartLine = OExcelSheet.StartLine;
            
            try
            {
                int SortIndex = 1;

                Worksheet sheet = (Worksheet)workBook.Sheets[OExcelSheet.SheetName];
                MapField[] MapFields = OExcelSheet.MapFields;
                string CommandText = "";

                while (true)
                {
                    object sCol1 = OExcelSheet.KeyField + StartLine.ToString();
                    object sCol2 = OExcelSheet.KeyField + StartLine.ToString();
                    object KeyValue = "";
                    if (sheet.get_Range(sCol1, sCol2) != null)
                    {
                        KeyValue = sheet.get_Range(sCol1, sCol2).Value2;
                    }
                    if (KeyValue != null)
                    {
                        string DeleteQuery =
                            " Delete from TableAssetListMadeBy where frkAssetListId in (select AssetListId from TableAssetList where InventoryNumber=@InventoryNumber); " +
                            " Delete from TableAssetList where InventoryNumber=@InventoryNumber and frkAssetTypeId=@AssetTypeId; ";
                        new SqlDatabase(ConnectionString).ExecuteQuery(null, ConnectionString, new DatabaseParamCls[] 
                                    { 
                                        new DatabaseParamCls("InventoryNumber", KeyValue),
                                        new DatabaseParamCls("AssetTypeId", OExcelSheet.AssetTypeId),
                                    }, DeleteQuery);

                    }
                    string AssetListId = System.Guid.NewGuid().ToString();
                    ProcessEvent("Line: " + +StartLine + "...");
                    try
                    {

                        for (int iColMapField = 0; iColMapField < MapFields.Length; iColMapField++)
                        {
                            string SqlField = MapFields[iColMapField].SQLDatabaseField;
                            SqlDbType sqldbType = MapFields[iColMapField].sqlDbType;

                            if (MapFields[iColMapField].ExcelColumn == null)
                            {
                                CommandText = " Update TableAssetList set [" + SqlField + "]=@" + SqlField + " where AssetListId=@AssetListId";
                                new SqlDatabase(ConnectionString).ExecuteQuery(oSqlTransaction, ConnectionString, new DatabaseParamCls[] 
                                    { 
                                        new DatabaseParamCls("AssetListId", AssetListId),
                                        new DatabaseParamCls(SqlField, sqldbType,MapFields[iColMapField].DefaultValue)
                                    }, CommandText);
                            }
                            else
                            {
                                object Col1 = MapFields[iColMapField].ExcelColumn + StartLine;
                                object Col2 = MapFields[iColMapField].ExcelColumn + StartLine;
                                object Value = "";
                                if (sheet.get_Range(Col1, Col2) != null)
                                {
                                    Value = sheet.get_Range(Col1, Col2).Value2;
                                }
                                if (Value == null) Value = "";

                                //ghi du lieu vao day...
                                //nhet bua 1 ban ghi vao
                                if (iColMapField == 0)
                                {
                                    CommandText = " Insert into TableAssetList(AssetListId,EntryDate, SessionId,SessionLog, ok, ExcelLine, SheetName, DayMonthYear,SortIndex) values (@AssetListId,GetDate(), @SessionId,'',1, @ExcelLine, @SheetName, '" + DayMonthYear + "',@SortIndex)";
                                    new SqlDatabase(ConnectionString).ExecuteQuery(oSqlTransaction, ConnectionString, new DatabaseParamCls[] 
                                    { 
                                        new DatabaseParamCls("AssetListId", AssetListId),
                                        new DatabaseParamCls("SessionId", SessionId),
                                        new DatabaseParamCls("ExcelLine", StartLine),
                                        new DatabaseParamCls("SheetName", OExcelSheet.SheetName),
                                        new DatabaseParamCls("SortIndex", SortIndex),
                                    }, CommandText);
                                }
                                if (sqldbType == SqlDbType.DateTime)
                                {
                                    try
                                    {
                                        if (Value.GetType() == typeof(double))
                                        {
                                            Value = DateTime.FromOADate(Double.Parse(Value.ToString()));
                                        }
                                        else
                                        {
                                            string[] All = Value.ToString().Split(new char[] { '/' }, StringSplitOptions.RemoveEmptyEntries);
                                            Value = new DateTime(int.Parse(All[2]), int.Parse(All[1]), int.Parse(All[0]));
                                        }
                                    }
                                    catch (Exception ex)
                                    {
                                        ex.Source = "";
                                        Value = System.DateTime.Now;
                                    }
                                }

                                //update du lieu...!
                                LookUpObject oLookUpObject = MapFields[iColMapField].oLookUpObject;
                                if (oLookUpObject == null)
                                {
                                    if (sqldbType == SqlDbType.Int)
                                    {
                                        try
                                        {
                                            int.Parse(Value.ToString());
                                        }
                                        catch
                                        {
                                            Value = 0;
                                        }
                                    }
                                    if (sqldbType == SqlDbType.Decimal)
                                    {
                                        try
                                        {
                                            decimal.Parse(Value.ToString());
                                        }
                                        catch
                                        {
                                            Value = 0;
                                        }
                                    }
                                    CommandText = " Update TableAssetList set [" + SqlField + "]=@" + SqlField + " where AssetListId=@AssetListId";
                                    try
                                    {
                                        new SqlDatabase(ConnectionString).ExecuteQuery(oSqlTransaction, ConnectionString, new DatabaseParamCls[] 
                                        { 
                                            new DatabaseParamCls("AssetListId", AssetListId),
                                            new DatabaseParamCls(SqlField, sqldbType,Value)
                                        }, CommandText);
                                    }
                                    catch (Exception ex)
                                    {
                                        if (sqldbType == SqlDbType.DateTime)
                                        {
                                        }
                                        else
                                        {
                                            throw new Exception(SqlField + ": " + ex.Message.ToString());
                                        }
                                    }
                                }
                                else
                                {
                                    //co doi tuong lookup
                                    //nhet vao bang lookup gia tri day..!
                                    string LookUpId = System.Guid.NewGuid().ToString();

                                    string[] LookUpdata = new string[] { Value.ToString() };
                                    string[] MulLookUpIds = new string[LookUpdata.Length];
                                    MulLookUpIds[0] = LookUpId;
                                    if (oLookUpObject.MultiLookUp)
                                    {
                                        LookUpdata = Value.ToString().Split(new char[] { ',', ';' }, StringSplitOptions.RemoveEmptyEntries);
                                        MulLookUpIds = new string[LookUpdata.Length];
                                    }
                                    for (int iIndexLookUp = 0; iIndexLookUp < LookUpdata.Length; iIndexLookUp++)
                                    {
                                        try
                                        {
                                            LookUpId = System.Guid.NewGuid().ToString();
                                            try
                                            {
                                                new SqlDatabase(ConnectionString).ExecuteInsertQuery(oSqlTransaction, ConnectionString, oLookUpObject.TabName, new DatabaseParamCls[]{
                                                    new DatabaseParamCls(oLookUpObject.KeyId,LookUpId),
                                                    new DatabaseParamCls(oLookUpObject.Text,LookUpdata[iIndexLookUp].Trim()),
                                                    });
                                            }
                                            catch (Exception ex)
                                            {
                                                if (sqldbType == SqlDbType.DateTime)
                                                {
                                                }
                                                else
                                                {
                                                    throw new Exception(SqlField + ": " + ex.Message.ToString());
                                                }
                                            }
                                            MulLookUpIds[iIndexLookUp] = LookUpId;
                                        }
                                        catch (Exception ex)
                                        {
                                            //lỗi đã có thì bố mày select keyid ra
                                            ex.Source = "";
                                            string SelectCommand = "select " + oLookUpObject.KeyId + " from " + oLookUpObject.TabName + " where " + oLookUpObject.Text + "=@Field";
                                            System.Data.DataTable dtResult = new SqlDatabase().GetRecordSet(
                                                oSqlTransaction,
                                                ConnectionString,
                                                SelectCommand,
                                                new DatabaseParamCls[]{
                                            new DatabaseParamCls("Field",LookUpdata[iIndexLookUp].Trim())
                                        });
                                            if (dtResult.Rows.Count == 0) throw new Exception("Có lỗi trong việc thiết lập multilookup!");
                                            LookUpId = (string)dtResult.Rows[0][oLookUpObject.KeyId];
                                            MulLookUpIds[iIndexLookUp] = (string)dtResult.Rows[0][oLookUpObject.KeyId];
                                        }
                                    }
                                    if (oLookUpObject.MultiLookUp == false)
                                    {
                                        Value = LookUpId;
                                        CommandText = " Update TableAssetList set [" + SqlField + "]=@" + SqlField + " where AssetListId=@AssetListId";
                                        try
                                        {
                                            new SqlDatabase(ConnectionString).ExecuteQuery(oSqlTransaction, ConnectionString, new DatabaseParamCls[] 
                                            { 
                                                new DatabaseParamCls("AssetListId", AssetListId),
                                                new DatabaseParamCls(SqlField, sqldbType,Value)
                                            }, CommandText);

                                            string SQLDataFieldDisplay = MapFields[iColMapField].SQLDataFieldDisplay;
                                            if (!string.IsNullOrEmpty(SQLDataFieldDisplay))
                                            {
                                                CommandText = " Update TableAssetList set [" + SQLDataFieldDisplay + "]=@" + SQLDataFieldDisplay + " where AssetListId=@AssetListId";
                                                new SqlDatabase(ConnectionString).ExecuteQuery(oSqlTransaction, ConnectionString, new DatabaseParamCls[] 
                                                { 
                                                    new DatabaseParamCls("AssetListId", AssetListId),
                                                    new DatabaseParamCls(SQLDataFieldDisplay, SqlDbType.NVarChar,LookUpdata[0])
                                                    }, CommandText);
                                            }
                                        }
                                        catch (Exception ex)
                                        {
                                            if (sqldbType == SqlDbType.DateTime)
                                            {
                                            }
                                            else
                                            {
                                                throw new Exception(SqlField + ": " + ex.Message.ToString());
                                            }
                                        }
                                    }
                                    else
                                    {
                                        //nhet vao bang trung gian da..
                                        for (int iIndexLookUpData = 0; iIndexLookUpData < LookUpdata.Length; iIndexLookUpData++)
                                        {
                                            try
                                            {
                                                string ItemMultiLookUpId = System.Guid.NewGuid().ToString();
                                                new SqlDatabase(ConnectionString).ExecuteInsertQuery(oSqlTransaction, ConnectionString, oLookUpObject.TabMulName, new DatabaseParamCls[]{
                                                    new DatabaseParamCls(oLookUpObject.TabMulKeyId,ItemMultiLookUpId),
                                                    new DatabaseParamCls(oLookUpObject.TabMulF1KeyId,MulLookUpIds[iIndexLookUpData]),
                                                    new DatabaseParamCls(oLookUpObject.TabMulF2KeyId,AssetListId),
                                                    });

                                            }
                                            catch (Exception ex)
                                            {
                                                ex.Source = "";
                                            }
                                        }

                                        //doan nay thi van update vao gia tri field binh thuong
                                        CommandText = " Update TableAssetList set [" + SqlField + "]=@" + SqlField + " where AssetListId=@AssetListId";
                                        try
                                        {
                                            new SqlDatabase(ConnectionString).ExecuteQuery(oSqlTransaction, ConnectionString, new DatabaseParamCls[] 
                                            { 
                                                new DatabaseParamCls("AssetListId", AssetListId),
                                                new DatabaseParamCls(SqlField, sqldbType,Value)
                                            }, CommandText);
                                        }
                                        catch (Exception ex)
                                        {
                                            if (sqldbType == SqlDbType.DateTime)
                                            {
                                            }
                                            else
                                            {
                                                throw new Exception(SqlField + ": " + ex.Message.ToString());
                                            }
                                        }
                                    }
                                }
                            }
                        }
                        StartLine++;
                        SortIndex++;

                        if (StartLine == OExcelSheet.EndLine) break;
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                }
                //cap nhat truong sap xep....!
                SqlConnection OSqlConnection = new SqlConnection(ConnectionString);
                OSqlConnection.Open();
                SqlCommand oSqlCommand = OSqlConnection.CreateCommand();
                oSqlCommand.CommandText = "select AssetListId, InventoryNumber, SortInventoryNumber from TableAssetList order by SortIndex";
                SqlDataAdapter sqlda = new SqlDataAdapter(oSqlCommand);
                System.Data.DataTable dtResultUpdate = new System.Data.DataTable();
                sqlda.Fill(dtResultUpdate);
                for (int iIndex = 0; iIndex < dtResultUpdate.Rows.Count; iIndex++)
                {
                    string AssetListId = (string)dtResultUpdate.Rows[iIndex]["AssetListId"];
                    string InventoryNumber = (string)dtResultUpdate.Rows[iIndex]["InventoryNumber"];
                    InventoryNumber = InventoryNumber.Trim();
                    string SInventoryNumber = ConvertSortField(InventoryNumber);
                    oSqlCommand.CommandText = " Update TableAssetList set SortInventoryNumber=@SortInventoryNumber where AssetListId=@AssetListId";
                    oSqlCommand.Parameters.Clear();
                    oSqlCommand.Parameters.Add("@AssetListId", SqlDbType.VarChar).Value = AssetListId;
                    oSqlCommand.Parameters.Add("@SortInventoryNumber", SqlDbType.NVarChar).Value = SInventoryNumber;
                    oSqlCommand.ExecuteNonQuery();
                }
                _excelApp.Visible = true;    
                ProcessEvent("Import dữ liệu thành công!");
            }
            catch (Exception ex)
            {
                _excelApp.Visible = true;
                RaiseProcessEvent("Dòng lỗi: " + StartLine + " -> " + ex.ToString());
            }
        }

        internal void ExecuteDelete(string AssTypeId, string AssTypeName)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            try
            {
                
                conn.Open();
                SqlCommand comm = new SqlCommand("select COUNT(AssetListId) from TableAssetList where frkAssetTypeId = '" + AssTypeId + "'", conn);
                Int32 count = (Int32)comm.ExecuteScalar();

                string DeleteQuery =
                                " Delete from TableAssetListMadeBy where frkAssetListId in (select AssetListId from TableAssetList where frkAssetTypeId=@AssetTypeId); " +
                                " Delete from TableAssetList where frkAssetTypeId=@AssetTypeId; ";
                new SqlDatabase(ConnectionString).ExecuteQuery(null, ConnectionString, new DatabaseParamCls[] 
                                    {             
                                        new DatabaseParamCls("AssetTypeId", AssTypeId),
                                    }, DeleteQuery);

                ProcessEvent("Xóa dữ liệu " + AssTypeName + " thành công");
                ProcessEvent("Số dòng: " + count);
            }
            catch (Exception ex)
            {
                RaiseProcessEvent("Lỗi: " + ex.ToString());
            }
            finally {
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }
    }
}
